/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Persistencia;

import Negocio.Bicicleta;
import Negocio.Pieza;
import Negocio.Usuario;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import java.util.List;

/**
 *
 * @author JAVA
 */
public class PBicicleta {

    public static void crearBicicletaBD(Bicicleta b, int id_usuario) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            
            statement.executeUpdate("INSERT INTO bicicleta (marca, modelo, tipo, id_usuario, rutaFoto) values ('"
                    + b.getMarca() + "', '" + b.getModelo() + "', "
                    + b.getTipo() + ", " + id_usuario + ", 'bici_default.jpg')");

            resultSet = statement.executeQuery("SELECT ID FROM bicicleta ORDER BY ID DESC");

            if (resultSet.next()) {
                b.setId(resultSet.getInt("id"));
            }

            if (b.getListPiezas() != null) {

                for (Pieza p : b.getListPiezas()) {
                    p.crearPieza(b.getId());
                }
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }

    public static void actualizarBicicletaBD(Bicicleta b) {

        Connection connection = null;
        Statement statement = null;


        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            statement.executeUpdate("UPDATE bicicleta "
                    + "SET marca = '" + b.getMarca()
                    + "', modelo = '" + b.getModelo()
                    + "', rutaFoto = '" + b.getRutafoto()
                    + "', tipo = " + b.getTipo()+ " WHERE id = " +b.getId());

            for (Pieza p : b.getListPiezas()) {
                p.actualizarPieza();
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {

                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }

    public static void eliminarBicicletaBD(Bicicleta b) {

        Connection connection = null;
        Statement statement = null;


        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            statement.executeUpdate("DELETE FROM BICICLETA WHERE ID = " + b.getId());

            for (Pieza p : b.getListPiezas()) {
                p.eliminarPieza();
            }
        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }

    public static Bicicleta seleccionarBicicletaBD(int id_bicicleta) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Bicicleta b = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            String SQL = "SELECT id, marca, modelo, tipo, rutaFoto from bicicleta where id =" + id_bicicleta;

            resultSet = statement.executeQuery(SQL);

            if (resultSet.next()) {
                b = new Bicicleta(resultSet.getInt("id"), resultSet.getString("marca"),
                        resultSet.getString("modelo"), resultSet.getInt("tipo"), resultSet.getString("rutaFoto"));
                List<Pieza> listPiezas = Pieza.devolverListaPiezas(b);
                b.setListPiezas(listPiezas);
            }
        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        return b;
    }
    
    
    public static List<Bicicleta> devolverListaBicicletaBD(Usuario u) {

        List<Bicicleta> listBicicletas = new ArrayList();

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            String SQL = "SELECT id from bicicleta where id_usuario =" + u.getId();

            resultSet = statement.executeQuery(SQL);

            while (resultSet.next()) {
                Bicicleta b = seleccionarBicicletaBD(resultSet.getInt("id"));
                listBicicletas.add(b);
            }
        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        return listBicicletas;
    }
}
